package cn.dao;

import cn.comon.DBConnection;
import cn.entity.Client;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ClientDao implements AutoCloseable {

    private Connection conn;

    public ClientDao() {
        // 获取数据库连接
        conn = new DBConnection().getConn();
    }

    // 添加客户
    public void addClient(List<Client> clients) {
        String sql = "INSERT INTO client (cid, cName, shortName, address, companyPhone, email, contact, contactPhone, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            conn.setAutoCommit(false);
            for (Client client : clients) {
                ps.setString(1, client.getCid());
                ps.setString(2, client.getCName());
                ps.setString(3, client.getShortName());
                ps.setString(4, client.getAddress());
                ps.setString(5, client.getCompanyPhone());
                ps.setString(6, client.getEmail());
                ps.setString(7, client.getContact());
                ps.setString(8, client.getContactPhone());
                ps.setString(9, client.getNotes());
                ps.addBatch();
            }
            ps.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException rollbackEx) {
                rollbackEx.printStackTrace();
            }
            e.printStackTrace();
        }
    }


    // 删除客户
    public void deleteClient(String cid) {
        String sql = "DELETE FROM client WHERE cid = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, cid);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 更新客户信息
    public void updateClient(Client client, String originalCid) {
        String sql = "UPDATE client SET cid = ?, cName = ?, shortName = ?, address = ?, companyPhone = ?, email = ?, contact = ?, contactPhone = ?, notes = ? WHERE cid = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, client.getCid());
            ps.setString(2, client.getCName());
            ps.setString(3, client.getShortName());
            ps.setString(4, client.getAddress());
            ps.setString(5, client.getCompanyPhone());
            ps.setString(6, client.getEmail());
            ps.setString(7, client.getContact());
            ps.setString(8, client.getContactPhone());
            ps.setString(9, client.getNotes());
            ps.setString(10, originalCid);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 查询所有客户
    public List<Object[]> selectAll() {
        List<Object[]> data = new ArrayList<>();
        String sql = "SELECT * FROM client";
        try (Statement sta = conn.createStatement();
             ResultSet rs = sta.executeQuery(sql)) {
            while (rs.next()) {
                Object[] rowData = new Object[9]; // 9字段对应客户信息的列数
                rowData[0] = rs.getString("cid");
                rowData[1] = rs.getString("cName");
                rowData[2] = rs.getString("shortName");
                rowData[3] = rs.getString("address");
                rowData[4] = rs.getString("companyPhone");
                rowData[5] = rs.getString("email");
                rowData[6] = rs.getString("contact");
                rowData[7] = rs.getString("contactPhone");
                rowData[8] = rs.getString("notes");
                data.add(rowData);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return data;
    }

    // 根据条件查询客户
    public List<Client> queryClients(Client criteria) {
        List<Client> clients = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM client WHERE 1=1");
        if (criteria.getCid() != null && !criteria.getCid().isEmpty()) {
            sql.append(" AND cid = ?");
        }
        if (criteria.getCName() != null && !criteria.getCName().isEmpty()) {
            sql.append(" AND cName = ?");
        }
        if (criteria.getShortName() != null && !criteria.getShortName().isEmpty()) {
            sql.append(" AND shortName = ?");
        }
        if (criteria.getAddress() != null && !criteria.getAddress().isEmpty()) {
            sql.append(" AND address = ?");
        }
        if (criteria.getCompanyPhone() != null && !criteria.getCompanyPhone().isEmpty()) {
            sql.append(" AND companyPhone = ?");
        }
        if (criteria.getEmail() != null && !criteria.getEmail().isEmpty()) {
            sql.append(" AND email = ?");
        }
        if (criteria.getContact() != null && !criteria.getContact().isEmpty()) {
            sql.append(" AND contact = ?");
        }
        if (criteria.getContactPhone() != null && !criteria.getContactPhone().isEmpty()) {
            sql.append(" AND contactPhone = ?");
        }
        if (criteria.getNotes() != null && !criteria.getNotes().isEmpty()) {
            sql.append(" AND notes = ?");
        }
        try (PreparedStatement ps = conn.prepareStatement(sql.toString())) {
            int index = 1;
            if (criteria.getCid() != null && !criteria.getCid().isEmpty()) {
                ps.setString(index++, criteria.getCid());
            }
            if (criteria.getCName() != null && !criteria.getCName().isEmpty()) {
                ps.setString(index++, criteria.getCName());
            }
            if (criteria.getShortName() != null && !criteria.getShortName().isEmpty()) {
                ps.setString(index++, criteria.getShortName());
            }
            if (criteria.getAddress() != null && !criteria.getAddress().isEmpty()) {
                ps.setString(index++, criteria.getAddress());
            }
            if (criteria.getCompanyPhone() != null && !criteria.getCompanyPhone().isEmpty()) {
                ps.setString(index++, criteria.getCompanyPhone());
            }
            if (criteria.getEmail() != null && !criteria.getEmail().isEmpty()) {
                ps.setString(index++, criteria.getEmail());
            }
            if (criteria.getContact() != null && !criteria.getContact().isEmpty()) {
                ps.setString(index++, criteria.getContact());
            }
            if (criteria.getContactPhone() != null && !criteria.getContactPhone().isEmpty()) {
                ps.setString(index++, criteria.getContactPhone());
            }
            if (criteria.getNotes() != null && !criteria.getNotes().isEmpty()) {
                ps.setString(index++, criteria.getNotes());
            }
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Client client = new Client();
                    client.setCid(rs.getString("cid"));
                    client.setCName(rs.getString("cName"));
                    client.setShortName(rs.getString("shortName"));
                    client.setAddress(rs.getString("address"));
                    client.setCompanyPhone(rs.getString("companyPhone"));
                    client.setEmail(rs.getString("email"));
                    client.setContact(rs.getString("contact"));
                    client.setContactPhone(rs.getString("contactPhone"));
                    client.setNotes(rs.getString("notes"));
                    clients.add(client);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return clients;
    }



    @Override
    public void close() {
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
